# @Project: diablo
# @File: get_quality_desc
# @Time: 2023/7/13 14:12
# @User: Jolly
# @Email: jolly@007vin.com
"""
# code is far away from bugs with the god animal protecting
    I love animals. They taste delicious.
              ┏┓      ┏┓
            ┏┛┻━━━┛┻┓
            ┃      ☃      ┃
            ┃  ┳┛  ┗┳  ┃
            ┃      ┻      ┃
            ┗━┓      ┏━┛
                ┃      ┗━━━┓
                ┃  神兽保佑    ┣┓
                ┃ 永无BUG！   ┏┛
                ┗┓┓┏━┳┓┏┛
                  ┃┫┫  ┃┫┫
                  ┗┻┛  ┗┻┛ 
"""

# 小汽配2.0获取分公司品质台账

import json
import pymysql
import time
import threading
from queue import Queue
from openpyxl import Workbook

import pandas as pd
from pymongo import MongoClient
from pymongo.errors import ConnectionFailure
from dbutils.pooled_db import PooledDB


lock = threading.Lock()


class GetSubCompanyQualityDesc:

    def __init__(self):

        start_time = time.time()

        self.quality_dict = {}

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 正式mysql
        self.mysql_conn = self.get_mysql_connection()

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=100,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 50  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)
        return pool

    # 根据分公司查询下属品质信息
    def get_quality_by_company_identify(self, identify):

        quality_data = []

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["ChangYuan_meta_stocks"]

                filter_dict = {
                    "app_key": "49914AB6-87D8-4AD7-A859-1DC970EAA8AD",
                    "erp_uid": identify
                }
                docs = conn.find(filter_dict, {'identify': 1, 'quality': 1, 'ori_quality': 1})

                for doc in docs:
                    identify_dict = {'identify': doc.get("identify", ""),
                                     'quality': doc.get("quality", ""),
                                     'ori_quality': doc.get("ori_quality", "")}

                    quality_data.append(identify_dict)

        except Exception as e:
            print("获取用户库存数据identify失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        # return quality_data

        # 没查找到数据的记录至文本中
        if len(quality_data) == 0:
            # with open("./search_fail_level1.txt", "a") as file:
            #     file.write(quality_desc + "\n")

            return False, False

        previous = quality_data[0]
        for row in quality_data:
            yield previous, False
            previous = row
        yield previous, True

    # 写入数据
    def write_data(self, *args):

        stock_data = args[0]
        sub_company_identify = args[1]

        # 定义子公司key名
        company_key_name = sub_company_identify.get("company_name") + "-" + sub_company_identify.get("stock_identify")

        ori_quality = stock_data.get("ori_quality")
        quality = stock_data.get("quality")

        # 定义品质key名
        quality_key_name = ori_quality + "-" + quality

        if self.quality_dict.get(company_key_name):
            # 判断是否包含quality
            sub_company_quality = self.quality_dict.get(company_key_name)

            if sub_company_quality.get(quality_key_name):
                # 如果包含的话，数量加一
                quality_key_data = sub_company_quality.get(quality_key_name)
                count = quality_key_data.get("count", 0)
                with lock:
                    quality_key_data["count"] = count + 1
            else:
                sub_company_quality[quality_key_name] = {
                    "ori_quality": ori_quality,
                    "quality": quality,
                    "count": 1
                }
        else:
            self.quality_dict[company_key_name] = {}
            self.quality_dict[company_key_name][quality_key_name] = {
                "ori_quality": ori_quality,
                "quality": quality,
                "count": 1
            }

    # 获取子公司列表
    def get_sub_company_list(self):

        stock_identify = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select stock_identify, company_name from diablo_company where status=1 and merchant_id = '3'"""

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    identify_ = {'stock_identify': result_[0],
                                 'company_name': result_[1]}

                    stock_identify.append(identify_)

        except Exception as e:
            print("获取用户分公司信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_identify

    # 任务执行
    def task(self):
        q = Queue(maxsize=200)  # 设定最大队列数和线程数

        count = 1

        # 获取分公司列表
        stock_identify = self.get_sub_company_list()

        for identify in stock_identify:

            # 根据分公司查询下属品质信息
            for row, last in self.get_quality_by_company_identify(identify.get("stock_identify")):

                if row is False:
                    continue

                t = threading.Thread(target=self.write_data, args=(row, identify))
                q.put(t)
                if (q.full() == True) or last:
                    thread_list = []
                    while q.empty() == False:
                        t = q.get()
                        thread_list.append(t)
                        t.start()
                    for t in thread_list:
                        t.join()

                print("记录数量为：", count, " 公司为：", identify.get('company_name'))

                if last:
                    break

                count += 1

        # with open("./sub_company_list.txt", "w", encoding='utf-8') as file:
        #     json.dump(self.quality_dict, file, ensure_ascii=False, indent=4)

        # 解析JSON数据
        data = json.loads(json.dumps(self.quality_dict, ensure_ascii=False, indent=4))

        # 创建新的Excel工作簿
        workbook = Workbook()
        # 选择默认的工作表
        sheet = workbook.active

        # 设置列名
        row = 1
        sheet.cell(row=row, column=1, value='Key')
        sheet.cell(row=row, column=2, value='ori_quality')
        sheet.cell(row=row, column=3, value='quality')
        sheet.cell(row=row, column=4, value='count')

        # 写入数据
        row = 2
        for key, value in data.items():
            for quality, info in value.items():
                try:
                    sheet.cell(row=row, column=1, value=str(key))
                    sheet.cell(row=row, column=2, value=info['ori_quality'])
                    sheet.cell(row=row, column=3, value=info['quality'])
                    sheet.cell(row=row, column=4, value=info['count'])
                    row += 1
                except Exception as e:
                    continue

        # 保存Excel文件
        workbook.save('sub_company_quality.xlsx')


if __name__ == "__main__":
    GetSubCompanyQualityDesc()



